import sqlite3
from datetime import datetime

try:
    # 使用上下文管理器自动处理连接
    with sqlite3.connect('ShopManagementSys.db') as conn:
        cursor = conn.cursor()

        # 创建表
        create_table_query = '''
            CREATE TABLE IF NOT EXISTS Product(
                product_id CHAR(4) NOT NULL PRIMARY KEY,
                product_name VARCHAR(100) NOT NULL,
                product_type VARCHAR(32) NOT NULL,
                sale_price REAL,
                purchase_price REAL,
                regist_date DATE
            )'''
        cursor.execute(create_table_query)
        print("表创建成功")

        # 插入单条数据（示例捕获主键冲突）
        insert_single_query = '''
            INSERT INTO Product 
            VALUES (?, ?, ?, ?, ?, ?)'''
        try:
            # 示例数据：未来日期触发警告
            regist_date = '2025-05-18'
            if datetime.strptime(regist_date, "%Y-%m-%d").date() > datetime.now().date():
                print("警告：登记日期为未来日期！")
            cursor.execute(insert_single_query, 
                ('P001', '智能手机', '电子产品', 5000.0, 4000.0, regist_date))
            conn.commit()
            print('单条数据插入成功')
        except sqlite3.IntegrityError as e:
            print(f"主键冲突: {e}")

        # 插入多条数据
        products = [
            ('P002', '笔记本电脑', '电子产品', 8000.0, 7000.0, '2025-05-18'),
            ('P003', '运动鞋', '服装', 500.0, 300.0, '2025-05-15')
        ]
        try:
            cursor.executemany(insert_single_query, products)
            conn.commit()
            print('多条数据插入成功')
        except sqlite3.Error as e:
            print(f"插入失败: {e}")

        # 查询所有数据
        cursor.execute("SELECT * FROM Product")
        print("\n所有产品:")
        for row in cursor.fetchall():
            print(row)

        # 聚合函数：平均价格
        cursor.execute("SELECT AVG(sale_price) FROM Product")
        avg_price = cursor.fetchone()[0]
        print(f"\n平均售价: ¥{round(avg_price, 2) if avg_price else 0}")

        # 排序：按价格降序
        cursor.execute("SELECT * FROM Product ORDER BY sale_price DESC")
        print("\n按价格降序:")
        for row in cursor.fetchall():
            print(row)

        # 分组统计
        cursor.execute("SELECT product_type, COUNT(*) FROM Product GROUP BY product_type")
        print("\n产品类型统计:")
        for type_, count in cursor.fetchall():
            print(f"{type_:<10}: {count} 个")

        # 模糊查询
        cursor.execute("SELECT * FROM Product WHERE product_name LIKE '%电脑%'")
        print("\n名称含'电脑'的产品:")
        #cursor.fetchall() 是游标（Cursor）的一个方法，它会一次性取出所有查询结果，并返回一个列表（list），列表中的每个元素是一行数据（通常是一个元组 tuple 或字典 dict，取决于游标的配置）。
        for row in cursor.fetchall():
            print(row)

except sqlite3.Error as e:
    print(f"数据库错误: {e}")